﻿Module Module1

    Dim Worksheet

    Function C(ByVal Cell As String)
        Return Worksheet.Range(Cell).Value
    End Function

    Function C(ByVal Col As String, ByVal Row As Integer)
        Return Worksheet.Range(Col & Trim(Row)).Value
    End Function

    Sub Main()
        OpenConnection("172.25.61.3", "ıstıhdam", "sa", "relayer", "ceren")
        Dim Excel = CreateObject("Excel.Application")
        For w = 1 To 3
            Dim Workbook = Excel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop & "\ceren\" & Format(w, "00") & ".2011 RAPOR.xlsx")
            Worksheet = Workbook.Worksheets(1)
            For i = 2 To 999
                If Trim(C("B", i)) <> "" Then
                    Console.WriteLine(C("B", i))
                    Dim rs = OpenSQL("select * from istihdam where 1 = 0")
                    rs.AddNew()
                    rs.SetValues("kimlik,adi,soyadi,cins,dogumtar", C("B", i), C("C", i), C("D", i), Mid(C("E", i), 1, 1), C("K", i))
                    rs.SetValues("egitim,meslekkod,faaliyet,basvurutar,uzatma", C("I", i), C("G", i), C("J", i), C("L", i), If(C("M", i) = "", "", "E"))
                    rs.Save()
                End If
            Next
        Next
        'For w = 12 To 12
        '    Dim Workbook = Excel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop & "\ceren\rapor2010" & Format(w, "00") & ".xls")
        '    For i = 1 To Workbook.Worksheets.Count
        '        Worksheet = Workbook.Worksheets(i)
        '        If Val(Worksheet.Name) > 0 Then
        '            Console.WriteLine(C("D9"))
        '            Dim rs = OpenSQL("select * from istihdam where 1 = 0")
        '            rs.AddNew()
        '            rs.SetValues("kimlik,ssno,adi,soyadi,cins,baba", C("d7"), C("d8"), C("d9"), C("d10"), Mid(C("e9"), 1, 1), C("d11"))
        '            rs.SetValues("adres,telefon,dogumyeri,dogumtar,uyruk", C("d12"), C("e12"), C("d13"), C("d14"), C("c15"))
        '            rs.SetValues("medeni,gys,boy,egitim,egitimbolum", C("c16"), C("d17"), C("d18"), C("c19"), C("d21"))
        '            rs.SetValues("yabancidil,diploma,ehliyet,issizlik,askerlik", C("d22"), C("d23"), C("d24"), C("c25"), C("d26"))
        '            rs.SetValues("tercih,meslek,meslekkod,ekmeslek,ekmeslekkod", C("d27"), C("d28"), C("e28"), C("d29"), C("e29"))
        '            rs.SetValues("faaliyet,basvurutar,durum", C("d30"), If(Not IsDate(C("d31")), Null, C("d31")), C("d32"))
        '            rs.Save()
        '        End If
        '    Next
        'Next
    End Sub

End Module
